package Application.Dao;
import java.sql.*;

import DomainData.Reservation;
import Reservation.MysqlConnection;
import Reservation.UserBean;
public class UserDAO {
	static Connection connection = null;
	static ResultSet rs = null;
	
	public static UserBean login(UserBean bean){
		Statement statement = null;
		
		String email = bean.getEmail();
		String password = bean.getPassword();
		
		String query = "Select * from user Where email='"+email+"' AND password='"+password+"'";
		System.out.println(query);
		
		try {
			connection = MysqlConnection.getConnection();
			if (connection==null){
				System.out.println("********Connection is null");
			}

			statement = connection.createStatement();
			rs = statement.executeQuery(query);
			boolean more = rs.next();
			if(!more)
			{
				System.out.println("Sorry, you are not a registered user!");
				bean.setValid(false);
			}
			else if(more){
				int userID = rs.getInt("userID");
				String firstName = rs.getString("firstName");
				String lastName = rs.getString("lastName");
				String phone = rs.getString("phone");
				String address = rs.getString("address");
				int identity = rs.getInt("identity");
				
				System.out.println("Welcome "+ firstName+"!");
				bean.setUserID(userID);
				bean.setFirstName(firstName);
				bean.setLastName(lastName);
				bean.setPhone(phone);
				bean.setAddress(address);
				bean.setValid(true);
				bean.setIdentity(identity);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//some exception handling
		finally
		{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					rs=null;
				}
			}
			if(statement!=null){
				try {
					statement.close();
				} catch (SQLException e) {
					statement=null;
				}
				
			}
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					connection=null;
				}
			}
		}
		return bean;
	}
	public static void createUser(UserBean user){
		
		try{
			// before Insert user data, get connection with database
			connection = MysqlConnection.getConnection();
			//try to write Insert query for preparing
			if(connection != null){
				java.sql.PreparedStatement stat = connection.prepareStatement("INSERT INTO user (firstName, lastName, email, phone, address, password,identity) "+
			"VALUES(?,?,?,?,?,?)");
				System.out.println(user.getUserID()+user.getFirstName()+user.getLastName()+user.getEmail());
				//stat.setInt(1, user.getUserID());
				stat.setString(1, user.getFirstName());
				stat.setString(2, user.getLastName());
				stat.setString(3, user.getEmail());
				stat.setString(4, user.getPhone());
				stat.setString(5, user.getAddress());
				stat.setString(6, user.getPassword());
				stat.setInt(7, 0);
				stat.executeUpdate();
				
				//ResultSet rs = stat.executeQuery();
				Statement statement = connection.createStatement();
				ResultSet rs =statement.executeQuery("select LAST_INSERT_ID()");
				int acctNumber=0;
				if(rs.next()){
					acctNumber = rs.getInt("last_insert_id()");
				}
				
				user.setUserID(acctNumber);
				
				System.out.println("You have successfully registered.");
				
			}
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println("Connection is wrong</br>");
			System.out.println(e.toString());
		}
		finally {
			try {
				connection.close();
				} catch(Exception ee) {};
			}
	}
	
	public static UserBean loadByID(int userID){
		UserBean user = new UserBean();
		Statement statement = null;
		String query = "SELECT * FROM `fkou`.`user` WHERE userID="+userID;
		/*System.out.println("Query:"+query);*/
		try {
			connection = MysqlConnection.getConnection();
			if (connection==null){
				System.out.println("********Connection is null");
			}

			statement = connection.createStatement();
			rs = statement.executeQuery(query);
			boolean more = rs.next();
			if(!more)
			{
				System.out.println("Sorry, you are not a registered user!");	
				return null;
			}
			else if(more){
				user.setUserID(rs.getInt("userID"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setEmail(rs.getString("email"));
				user.setPhone(Integer.toString(rs.getInt("phone")));
				user.setAddress(rs.getString("address"));
				user.setPassword(rs.getString("password"));
			}
			
			
		}catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
		
	}
	public static UserBean loadByEmail(String email){
		UserBean user = new UserBean();
		Statement statement = null;
		String query = "SELECT * FROM `fkou`.`user` WHERE email="+email;
		/*System.out.println("Query:"+query);*/
		try {
			connection = MysqlConnection.getConnection();
			if (connection==null){
				System.out.println("********Connection is null");
			}

			statement = connection.createStatement();
			rs = statement.executeQuery(query);
			boolean more = rs.next();
			if(!more)
			{
				System.out.println("Sorry, you are not a registered user!");	
				return null;
			}
			else if(more){
				user.setUserID(rs.getInt("userID"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setEmail(rs.getString("email"));
				user.setPhone(Integer.toString(rs.getInt("phone")));
				user.setAddress(rs.getString("address"));
				user.setPassword(rs.getString("password"));
			}
			
			
		}catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
		
	}
	
}
